This example demonstrates the benefits of writing record selection formulas that can be pushed down to the database server.
In the Orders table of the Xtreme sample database, there are 2,001 records, of which 169 have order dates prior to 1997. Suppose you want to report on only those records. On the one hand, you could use this record selection formula:
Year ({Orders.Order Date}) < 1997
The SQL query generated will send all 2,001 records to Crystal Reports, and then the record selection formula will reduce this to 169. To see this, click Show SQL Query on the Database menu and notice that the SQL query has no WHERE clause. This is because Crystal Reports is not able to push down the Year ( ) function in the WHERE clause.
On the other hand, this record selection formula generates the same report:
{Orders.Order Date} < #Jan 1, 1997#
This second formula, however, can be performed on the database server, so it is pushed down. The SQL query generated will send only 169 records to Crystal Reports. So, when the record selection formula is evaluated by Crystal Reports, no further records need to be eliminated. Click Show SQL Query on the Database menu and notice that the resulting SQL query has a WHERE clause.
As this example shows, your report's processing speed improves when you enhance your record selection formula. In this case, both formulas generate the same report, but the second takes advantages of the power and optimizations that the database server can use when handling its own data.
To learn more about writing efficient record selection formulas, be sure to read the rest of this section: Using enhanced record selection formulas.
Note: If you are new to record selection formulas, you may prefer to begin with the Select Expert or the sample record selection formula templates. For further details, along with an introduction to record selection, see Selecting records.
Seagate Software IMG Holdings, Inc. http://www.seagatesoftware.com Support services: http://support.seagatesoftware.com |